/* Table Updates */

USE Daycare
GO

/* Drop tables to get fresh start */



IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'EmployeeDocuments' )) 
  BEGIN
    DROP TABLE daycare.EmployeeDocuments
  END
  
IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ConfigurationSettings' )) 
  BEGIN
    DROP TABLE daycare.ConfigurationSettings
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ConfigurationSettingsGroups' )) 
  BEGIN
    DROP TABLE daycare.ConfigurationSettingsGroups
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'AgeGroups' )) 
  BEGIN
    DROP TABLE daycare.AgeGroups
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ConfigurationSettingsGroups' )) 
  BEGIN
    DROP TABLE daycare.ConfigurationSettingsGroups
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ConfigurationSettingsGroups' )) 
  BEGIN
    DROP TABLE daycare.ConfigurationSettingsGroups
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'NotificationSettingEmailList' )) 
  BEGIN
    DROP TABLE daycare.NotificationSettingEmailList
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'NotificationSettings' )) 
  BEGIN
    DROP TABLE daycare.NotificationSettings
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'NotificationTypes' )) 
  BEGIN
    DROP TABLE daycare.NotificationTypes
  END


IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'FamilyDocuments' )) 
  BEGIN
    DROP TABLE daycare.FamilyDocuments
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'FamilyNotes' )) 
  BEGIN
    DROP TABLE daycare.FamilyNotes
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ChildAllergies' )) 
  BEGIN
    DROP TABLE daycare.ChildAllergies
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Allergies' )) 
  BEGIN
    DROP TABLE daycare.Allergies
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'MedicationReminders' )) 
  BEGIN
    DROP TABLE daycare.MedicationReminders
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ChildMedications' )) 
  BEGIN
    DROP TABLE daycare.ChildMedications
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Medications' )) 
  BEGIN
    DROP TABLE daycare.Medications
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ChildNotes' )) 
  BEGIN
    DROP TABLE daycare.ChildNotes
  END

  
IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ChildInsuranceDetails' )) 
  BEGIN
    DROP TABLE daycare.ChildInsuranceDetails
  END

  
IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Hospitals' )) 
  BEGIN
    DROP TABLE daycare.Hospitals
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Children' )) 
  BEGIN
    DROP TABLE daycare.Children
  END

  


IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ReminderTimes' )) 
  BEGIN
    DROP TABLE daycare.ReminderTimes
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Reminders' )) 
  BEGIN
    DROP TABLE daycare.Reminders
  END



IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ChildContacts' )) 
  BEGIN
    DROP TABLE daycare.ChildContacts
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ChildContactTypes' )) 
  BEGIN
    DROP TABLE daycare.ChildContactTypes
  END
  
  IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ChildRelationships' )) 
  BEGIN
    DROP TABLE daycare.ChildRelationships
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'ChildContacts' )) 
  BEGIN
    DROP TABLE daycare.ChildContacts
  END
  
IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Families' )) 
  BEGIN
    DROP TABLE daycare.Families
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Addresses' )) 
  BEGIN
    DROP TABLE daycare.Addresses
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'AddressTypes' )) 
  BEGIN
    DROP TABLE daycare.AddressTypes
  END
  
IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'EmployeeNotes' )) 
  BEGIN
    DROP TABLE daycare.EmployeeNotes
  END


IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Employees' )) 
  BEGIN
    DROP TABLE daycare.Employees
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Documents' )) 
  BEGIN
    DROP TABLE daycare.Documents
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'DocumentTypes' )) 
  BEGIN
    DROP TABLE daycare.DocumentTypes
  END

  
IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'Images' )) 
  BEGIN
    DROP TABLE daycare.Images
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'MedicalProfessionals' )) 
  BEGIN
    DROP TABLE daycare.MedicalProfessionals
  END

IF (EXISTS ( SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_SCHEMA = 'daycare'
                    AND TABLE_NAME = 'MedicalProfessionalTypes' )) 
  BEGIN
    DROP TABLE daycare.MedicalProfessionalTypes
  END


GO
/*********************************************/

CREATE TABLE daycare.Images
  (
   ImageId INT NOT NULL
               IDENTITY(1,1)
               PRIMARY KEY
 , FileName VARCHAR(200) NOT NULL
  )

CREATE TABLE daycare.Employees
  (
   EmployeeId INT NOT NULL
                  IDENTITY(2,1)
                  PRIMARY KEY
 , FirstName VARCHAR(50) NOT NULL
 , MiddleInitial CHAR
 , LastName VARCHAR(50) NOT NULL
 , PhoneNumber VARCHAR(15)
 , EmailAddress VARCHAR(100)
 , IsEmployed BIT NOT NULL
 , ImageId INT NOT NULL
  )

ALTER TABLE daycare.Employees ADD CONSTRAINT FK_Employees_Images FOREIGN KEY (ImageId) REFERENCES daycare.Images(ImageId)
GO


CREATE TABLE daycare.DocumentTypes
  (
   DocumentTypeId INT NOT NULL
                      IDENTITY(1,1)
                      PRIMARY KEY
 , DocumentTypeName VARCHAR(50) NOT NULL
  )


CREATE TABLE daycare.Documents
  (
   DocumentId INT NOT NULL
                  IDENTITY(1,1)
                  PRIMARY KEY
 , DocumentName VARCHAR(100) NOT NULL
 , FileName VARCHAR(200) NOT NULL
 , DocumentTypeId INT NOT NULL
 , ExpirationDate DATE
  )

ALTER TABLE daycare.Documents ADD CONSTRAINT FK_Documents_DocumentTypes FOREIGN KEY (DocumentTypeId) REFERENCES daycare.DocumentTypes (DocumentTypeId)
GO

CREATE TABLE daycare.EmployeeDocuments
  (
   EmployeeDocumentId INT NOT NULL
                          IDENTITY(1,1)
                          PRIMARY KEY
 , EmployeeId INT NOT NULL
 , DocumentId INT NOT NULL
  )

ALTER TABLE daycare.EmployeeDocuments ADD CONSTRAINT FK_EmployeeEmployeeDocuments_Documents FOREIGN KEY (DocumentId) REFERENCES daycare.Documents (DocumentId)
GO
ALTER TABLE daycare.EmployeeDocuments ADD CONSTRAINT FK_EmployeeEmployeeDocuments_Employees FOREIGN KEY (EmployeeId) REFERENCES daycare.Employees (EmployeeId)
GO

CREATE TABLE daycare.ConfigurationSettingsGroups
  (
   ConfigurationSettingsGroupId INT NOT NULL
                                    PRIMARY KEY
                                    IDENTITY(1,1)
 , ConfigurationSettingsGroupName VARCHAR(100) NOT NULL
  )
GO

CREATE TABLE daycare.ConfigurationSettings
  (
   ConfigurationSettingsId INT NOT NULL
                               PRIMARY KEY
                               IDENTITY(1,1)
 , ConfigurationSettingsName VARCHAR(100) NOT NULL
 , ConfigurationSettingsDescription VARCHAR(MAX) NULL
 , ConfigurationSettingsGroupId INT NOT NULL
  )
ALTER TABLE daycare.ConfigurationSettings ADD CONSTRAINT FK_ConfigurationSettings_ConfigurationSettingsGroups FOREIGN KEY (ConfigurationSettingsGroupId) REFERENCES daycare.ConfigurationSettingsGroups (ConfigurationSettingsGroupId)
GO

CREATE TABLE daycare.AddressTypes
  (
   AddressTypeId INT NOT NULL
                     PRIMARY KEY
                     IDENTITY(1,1)
 , AddressTypeName VARCHAR(100) NOT NULL
  )


CREATE TABLE daycare.Addresses
  (
   AddressId INT NOT NULL
                 IDENTITY(1,1)
                 PRIMARY KEY
 , StreetAddressOne VARCHAR(200) NULL
 , StreetAddressTwo VARCHAR(200)
 , City VARCHAR(100) NULL
 , State VARCHAR(2) NULL
 , ZipCode VARCHAR(10) NULL
 , AddressTypeId INT NOT NULL
  )

ALTER TABLE daycare.Addresses ADD CONSTRAINT FK_Addresses_AddressTypes FOREIGN KEY(AddressTypeId) REFERENCES daycare.AddressTypes(AddressTypeId)
GO

CREATE TABLE daycare.Families
  (
   FamilyId INT NOT NULL
                IDENTITY(1,1)
                PRIMARY KEY
 , IsActive BIT NOT NULL
 , EnforceApprovedPickupList BIT NOT NULL
 , MailingAddressId INT
 , BillingAddressId INT
  )

ALTER TABLE daycare.Families ADD CONSTRAINT FK_Families_Addresses_One FOREIGN KEY(MailingAddressId) REFERENCES daycare.Addresses(AddressId)
ALTER TABLE daycare.Families ADD CONSTRAINT FK_Families_Addresses_Two FOREIGN KEY(BillingAddressId) REFERENCES daycare.Addresses(AddressId)
GO


CREATE TABLE daycare.AgeGroups
  (
   AgeGroupId INT NOT NULL
                  IDENTITY(1,1)
                  PRIMARY KEY
 , AgeMin INT NOT NULL
 , AgeMax INT NOT NULL
 , ChildrenPerStaffMember INT NOT NULL
  )

CREATE TABLE daycare.Children
  (
   ChildId INT NOT NULL
               IDENTITY(1,1)
               PRIMARY KEY
 , FamilyId INT NOT NULL
 , FirstName VARCHAR(100) NOT NULL
 , MiddleName VARCHAR(100) NULL
 , LastName VARCHAR(100) NOT NULL
 , NickName VARCHAR(100)
 , DateOfBirth DATE NOT NULL
 , IsActive BIT NOT NULL
 , ImageId INT NOT NULL
  )
ALTER TABLE daycare.Children ADD CONSTRAINT FK_Children_Images FOREIGN KEY(ImageId) REFERENCES daycare.Images(ImageId)
ALTER TABLE daycare.Children ADD CONSTRAINT FK_Children_Families FOREIGN KEY(FamilyId) REFERENCES daycare.Families(FamilyId)
GO


CREATE TABLE daycare.ChildContactTypes
(
ChildContactTypeId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
ChildContactTypeName VARCHAR(100) NOT NULL
)
GO

CREATE TABLE daycare.ChildRelationships
(
ChildRelationshipId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
ChildRelationshipName VARCHAR(100) NOT NULL
)
GO

CREATE TABLE daycare.ChildContacts
  (
   ChildContactId INT NOT NULL
               IDENTITY(1,1)
               PRIMARY KEY
 , ChildContactTypeId INT NOT NULL
 , FamilyId INT NOT NULL
 , ChildRelationshipid INT NOT NULL
 , FirstName VARCHAR(100) NOT NULL
 , MiddleName VARCHAR(100) NULL
 , LastName VARCHAR(100) NOT NULL
 , HomePhoneNumber VARCHAR(15)
 , WorkPhoneNumber VARCHAR(15)
 , CellPhoneNumber VARCHAR(15)
 , EmailAddress VARCHAR(100)
 , IsEmergencyContact BIT NOT NULL
 , IsApprovedPickup BIT NOT NULL
 , ImageId INT
 , ReceivesEmailNotifications BIT NOT NULL
 , ReceivesPostalNotifications BIT NOT NULL
 , BillingAddressId INT
 , MailingAddressId INT
  )
ALTER TABLE daycare.ChildContacts ADD CONSTRAINT FK_Adults_Images FOREIGN KEY(ImageId) REFERENCES daycare.Images(ImageId)
ALTER TABLE daycare.ChildContacts ADD CONSTRAINT FK_Adults_Families FOREIGN KEY(FamilyId) REFERENCES daycare.Families(FamilyId)
ALTER TABLE daycare.ChildContacts ADD CONSTRAINT FK_Adults_ChildRelationships FOREIGN KEY(ChildRelationshipId) REFERENCES daycare.ChildRelationships(ChildRelationshipId)
ALTER TABLE daycare.ChildContacts ADD CONSTRAINT FK_Adults_ChildContactTypeId FOREIGN KEY(ChildContactTypeId) REFERENCES daycare.ChildContactTypes(ChildContactTypeId)
ALTER TABLE daycare.ChildContacts ADD CONSTRAINT FK_Adults_Addresses_Billing FOREIGN KEY(BillingAddressId) REFERENCES daycare.Addresses(AddressId)
ALTER TABLE daycare.ChildContacts ADD CONSTRAINT FK_Adults_Addresses_Mailing FOREIGN KEY(MailingAddressId) REFERENCES daycare.Addresses(AddressId)
GO


CREATE TABLE daycare.Allergies
  (
   AllergyId INT NOT NULL
                 IDENTITY(1,1)
                 PRIMARY KEY
 , AllergyName VARCHAR(100) NOT NULL
  )

CREATE TABLE daycare.ChildAllergies
  (
   ChildAllergyId INT NOT NULL
                      PRIMARY KEY
                      IDENTITY(1,1)
 , AllergyId INT NOT NULL
 , ChildID INT NOT NULL
 , AllergyNote VARCHAR(255)
  )
ALTER TABLE daycare.ChildAllergies ADD CONSTRAINT FK_ChildAllergies_Allergies FOREIGN KEY (AllergyId) REFERENCES daycare.Allergies(AllergyId)
ALTER TABLE daycare.ChildAllergies ADD CONSTRAINT FK_ChildAllergies_Children FOREIGN KEY (ChildId) REFERENCES daycare.Children(ChildId)
GO

CREATE TABLE daycare.Medications
  (
   MedicationId INT NOT NULL
                    IDENTITY(1,1)
                    PRIMARY KEY
 , MedicationName VARCHAR(100) NOT NULL
  )

CREATE TABLE daycare.ChildMedications
  (
   ChildMedicationId INT NOT NULL
                         IDENTITY(1,1)
                         PRIMARY KEY
 , ChildId INT NOT NULL
 , MedicationId INT NOT NULL
 , Dosage VARCHAR(100) NOT NULL
  )
ALTER TABLE daycare.ChildMedications ADD CONSTRAINT FK_ChildMedications_Medications FOREIGN KEY (MedicationId) REFERENCES daycare.Medications(MedicationId)
ALTER TABLE daycare.ChildMedications ADD CONSTRAINT FK_ChildMedications_Children FOREIGN KEY (ChildId) REFERENCES daycare.Children(ChildId)
GO

CREATE TABLE daycare.ChildNotes
  (
   ChildNoteId INT NOT NULL
                   IDENTITY(1,1)
                   PRIMARY KEY
 , TimeStamp DATETIME2 NOT NULL
 , ChildId INT NOT NULL
 , NotedById INT NOT NULL
 , NoteText VARCHAR(1000) NOT NULL
  )
ALTER TABLE daycare.ChildNotes ADD CONSTRAINT DF_ChildNotes_TimeStamp DEFAULT GETDATE() FOR TimeStamp
ALTER TABLE daycare.ChildNotes ADD CONSTRAINT FK_ChildNotes_Children FOREIGN KEY(ChildId) REFERENCES daycare.Children(ChildId)
ALTER TABLE daycare.ChildNotes ADD CONSTRAINT FK_ChildNotes_Employees FOREIGN KEY(NotedById) REFERENCES daycare.Employees(EmployeeId)
GO

CREATE TABLE daycare.Reminders
  (
   ReminderId INT NOT NULL
                  PRIMARY KEY
                  IDENTITY(1,1)
 , StartDate DATE NOT NULL
 , EndDate DATE NOT NULL
  )

CREATE TABLE daycare.MedicationReminders
  (
   MedicationReminderId INT NOT NULL
                            IDENTITY(1,1)
                            PRIMARY KEY
 , ReminderId INT NOT NULL
 , ChildMedicationId INT NOT NULL
  )
ALTER TABLE daycare.MedicationReminders ADD CONSTRAINT FK_MedicationReminders_Reminder FOREIGN KEY(ReminderId) REFERENCES daycare.Reminders(ReminderId)
ALTER TABLE daycare.MedicationReminders ADD CONSTRAINT FK_MedicationReminders_ChildMedication FOREIGN KEY(ChildMedicationId) REFERENCES daycare.ChildMedications(ChildMedicationId)
GO

CREATE TABLE daycare.ReminderTimes
  (
   ReminderTimeId INT NOT NULL
                      PRIMARY KEY
                      IDENTITY(1,1)
 , ReminderId INT NOT NULL
 , ReminderTime TIME NOT NULL
 , AnsweredById INT
 , AnsweredTimeStamp DATETIME2
  )
ALTER TABLE daycare.ReminderTimes ADD CONSTRAINT FK_ReminderTimes_Reminder FOREIGN KEY (ReminderId) REFERENCES daycare.Reminders(ReminderId)
ALTER TABLE daycare.ReminderTimes ADD CONSTRAINT FK_ReminderTimes_Employees FOREIGN KEY (AnsweredById) REFERENCES daycare.Employees(EmployeeId)
GO

CREATE TABLE daycare.FamilyNotes
  (
   FamilyNoteId INT NOT NULL
                    IDENTITY(1,1)
                    PRIMARY KEY
 , TimeStamp DATETIME2 NOT NULL
 , FamilyId INT NOT NULL
 , NotedById INT NOT NULL
 , NoteText VARCHAR(1000) NOT NULL
  )
ALTER TABLE daycare.FamilyNotes ADD CONSTRAINT DF_FamilyNotes_TimeStamp DEFAULT GETDATE() FOR TimeStamp
ALTER TABLE daycare.FamilyNotes ADD CONSTRAINT FK_FamilyNotes_Families FOREIGN KEY(FamilyId) REFERENCES daycare.Families(FamilyId)
ALTER TABLE daycare.FamilyNotes ADD CONSTRAINT FK_FamilyNotes_Employees FOREIGN KEY(NotedById) REFERENCES daycare.Employees(EmployeeId)
GO

CREATE TABLE daycare.NotificationTypes
  (
   NotificationTypeId INT NOT NULL
                          PRIMARY KEY
                          IDENTITY(1,1)
 , NotificationTypeName VARCHAR(100) NOT NULL
  )

CREATE TABLE daycare.NotificationSettings
  (
   NotificationSettingId INT NOT NULL
                             IDENTITY(1,1)
                             PRIMARY KEY
 , FamilyId INT NOT NULL
 , NotificationTypeId INT NOT NULL
 , ReceivePostal BIT NOT NULL
 , ReceiveEmail BIT NOT NULL
 , ReceivePrinted BIT NOT NULL
  )
ALTER TABLE daycare.NotificationSettings ADD CONSTRAINT FK_NotificationSettings_Families FOREIGN KEY(FamilyId) REFERENCES daycare.Families(FamilyId)
ALTER TABLE daycare.NotificationSettings ADD CONSTRAINT FK_NotificationSettings_NotificationTypes FOREIGN KEY(NotificationTypeId) REFERENCES daycare.NotificationTypes(NotificationTypeId)
GO

CREATE TABLE daycare.NotificationSettingEmailList
  (
   NotificationSettingEmailListId INT NOT NULL
                                      PRIMARY KEY
                                      IDENTITY(1,1)
 , NotificationSettingId INT NOT NULL
 , ChildContactId INT NOT NULL
  )
ALTER TABLE daycare.NotificationSettingEmailList ADD CONSTRAINT FK_NotificationSettingEmailList_NotificationSettings FOREIGN KEY(NotificationSettingId) REFERENCES daycare.NotificationSettings(NotificationSettingId)
ALTER TABLE daycare.NotificationSettingEmailList ADD CONSTRAINT FK_NotificationSettingEmailList_ChildContacts FOREIGN KEY(ChildContactId) REFERENCES daycare.ChildContacts(ChildContactId)
GO

CREATE TABLE daycare.FamilyDocuments
  (
   FamilyDocumentId INT NOT NULL
                        PRIMARY KEY
                        IDENTITY(1,1)
 , FamilyId INT NOT NULL
 , DocumentId INT NOT NULL
 , ExpirationDate DATE
 , DocumentName VARCHAR(100) NOT NULL
 , FilePath VARCHAR(200) NOT NULL
  )
ALTER TABLE daycare.FamilyDocuments ADD CONSTRAINT FK_FamilyDocuments_Families FOREIGN KEY(FamilyId) REFERENCES daycare.Families(FamilyId)
ALTER TABLE daycare.FamilyDocuments ADD CONSTRAINT FK_FamilyDocuments_Documents FOREIGN KEY(DocumentId) REFERENCES daycare.Documents(DocumentId)
GO

CREATE TABLE daycare.MedicalProfessionalTypes
(
MedicalProfessionalTypeId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
MedicalProfessionalTypeName VARCHAR(50) NOT NULL
)
GO

CREATE TABLE daycare.Hospitals
(
HospitalId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
HospitalName VARCHAR(200) NOT NULL
)

CREATE TABLE daycare.MedicalProfessionals
(
MedicalProfessionalId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
WorkPhoneNumber VARCHAR(20) NOT NULL,
MedicalProfessionalTypeId INT NOT NULL
)
ALTER TABLE daycare.MedicalProfessionals ADD CONSTRAINT FK_MedicalProfessionals_MedicalProfessionalTypes FOREIGN KEY(MedicalPRofessionalTypeId) REFERENCES daycare.MedicalPRofessionalTypes(MedicalProfessionalTypeId)

GO
CREATE TABLE daycare.ChildInsuranceDetails
(
ChildInsuranceDetailsId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ChildId INT NOT NULL,
PreferredHospitalId INT,
MedicalInsuranceProvider VARCHAR(200),
FamilyDoctorId INT,
DentalInsuranceProvider VARCHAR(200),
FamilyDentistId INT 
)
ALTER TABLE daycare.ChildInsuranceDetails ADD CONSTRAINT FK_ChildInsuranceDetails_Children FOREIGN KEY(ChildId) REFERENCES daycare.Children(ChildId)
ALTER TABLE daycare.ChildInsuranceDetails ADD CONSTRAINT FK_ChildInsuranceDetails_Hospitals FOREIGN KEY(PreferredHospitalId) REFERENCES daycare.Hospitals(HospitalId)
ALTER TABLE daycare.ChildInsuranceDetails ADD CONSTRAINT FK_ChildInsuranceDetails_MedicalProfessionals_Doctor FOREIGN KEY(FamilyDoctorId) REFERENCES daycare.MedicalProfessionals(MedicalProfessionalId)
ALTER TABLE daycare.ChildInsuranceDetails ADD CONSTRAINT FK_ChildInsuranceDetails_MedicalProfessionals_Dentist FOREIGN KEY(FamilyDentistId) REFERENCES daycare.MedicalProfessionals(MedicalProfessionalId)
GO

CREATE TABLE [Daycare].[EmployeeNotes](
	[EmployeeNoteId] [int] IDENTITY(1,1) NOT NULL,
	[TimeStamp] [datetime2](7) NOT NULL,
	[EmployeeId] [int] NOT NULL,
	[NotedById] [int] NOT NULL,
	[NoteText] [varchar](1000) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[EmployeeNoteId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Daycare].[EmployeeNotes] ADD  CONSTRAINT [DF_EmployeeNotes_TimeStamp]  DEFAULT (getdate()) FOR [TimeStamp]

ALTER TABLE [Daycare].[EmployeeNotes]  WITH CHECK ADD  CONSTRAINT [FK_EmplpoyeeNotes_Employees_NotedBy] FOREIGN KEY([NotedById])
REFERENCES [Daycare].[Employees] ([EmployeeId])

ALTER TABLE [Daycare].[EmployeeNotes]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeNotes_Employees_Staff] FOREIGN KEY([EmployeeId])
REFERENCES [Daycare].[Employees] ([EmployeeId])
GO

/* Stored Procedures */

USE Daycare
GO


IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetEmployees' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetEmployees 
  AS 
  SELECT 1
  ') 
GO

ALTER PROCEDURE daycare.GetEmployees
AS 
  SELECT  EmployeeId
        , FirstName
		, MiddleInitial
        , LastName
        , PhoneNumber
        , EmailAddress
        , IsEmployed
        , emp.ImageId
        , usr.UserName
        , img.FileName
  FROM    daycare.Employees AS emp
  INNER JOIN dbo.aspnet_Profile AS pro ON CONVERT(VARCHAR(10),emp.EmployeeId) LIKE pro.PropertyValuesString
  INNER JOIN dbo.aspnet_Users AS usr ON pro.UserId = usr.UserId
  INNER JOIN daycare.Images AS img ON emp.ImageId = img.ImageId
  ORDER BY Firstname
        , LastName
  
  GO


IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'UpdateEmployee' ) 
  EXEC('
  CREATE PROCEDURE daycare.UpdateEmployee 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.UpdateEmployee
  @EmployeeId INT
, @FirstName VARCHAR(100)
, @MiddleInitial CHAR
, @LastName VARCHAR(100)
, @PhoneNumber VARCHAR(15)
, @EmailAddress VARCHAR(100)
, @IsEmployed BIT
, @ImageId INT
AS 
  UPDATE  daycare.Employees
  SET     FirstName = @FirstName
		, MiddleInitial = @MiddleInitial
        , LastName = @LastName
        , PhoneNumber = @PhoneNumber
        , EmailAddress = @EmailAddress
        , IsEmployed = @IsEmployed
        , ImageId = @ImageId
  WHERE   EmployeeId = @EmployeeId
  GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'AddEmployee' ) 
  EXEC('
  CREATE PROCEDURE daycare.AddEmployee 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.AddEmployee
  @FirstName VARCHAR(100)
, @MiddleInitial CHAR
, @LastName VARCHAR(100)
, @PhoneNumber VARCHAR(15)
, @EmailAddress VARCHAR(100)
, @IsEmployed BIT
, @ImageId INT
, @UName VARCHAR(100)
, @Pwd VARCHAR(100)
, @Salt VARCHAR(100)
AS 
  BEGIN
    INSERT  INTO daycare.Employees
            (
             FirstName
		   , MiddleInitial           
           , LastName
           , PhoneNumber
           , EmailAddress
           , IsEmployed
           , ImageId
            )
    VALUES  (
             @FirstName
		   , @MiddleInitial
           , @LastName
           , @PhoneNumber
           , @EmailAddress
           , @IsEmployed
           , @ImageId
            )

    DECLARE @EmployeeId INT = SCOPE_IDENTITY();
    DECLARE @EmployeeIdString VARCHAR(10) = (CONVERT(VARCHAR(10),@EmployeeId));
    DECLARE @EmployeeProfileProperty VARCHAR(50) = 'EmployeeId:S:0:' + CONVERT(VARCHAR(10),LEN(@EmployeeId))
    DECLARE @AppName VARCHAR(100) = 'Daycare';
    DECLARE @AppId UNIQUEIDENTIFIER = (
                                       SELECT ApplicationId
                                       FROM   dbo.aspnet_Applications
                                       WHERE  ApplicationName = @AppName
                                      )
    DECLARE @Timestamp DATETIME = GETDATE();
    DECLARE @UserId UNIQUEIDENTIFIER;
  
    EXEC dbo.aspnet_Membership_CreateUser @ApplicationName = @AppName,@UserName = @UName,@Password = @Pwd,@PasswordSalt = @Salt,@Email = @EmailAddress,@PasswordQuestion = '',@PasswordAnswer = '',@IsApproved = 1,@CurrentTimeUtc = @Timestamp,@CreateDate = @Timestamp,@UniqueEmail = 1,@PasswordFormat = 1,@UserId = @UserId
    EXEC dbo.aspnet_Profile_SetProperties @ApplicationName = @AppName,@PropertyNames = @EmployeeProfileProperty,@PropertyValuesString = @EmployeeIdString,@PropertyValuesBinary = '0x',@UserName = @UName,@IsUserAnonymous = 0,@CurrentTimeUtc = @Timestamp

  END
 GO


IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'dbo'
                        AND obj.name = 'UpdateSalt' ) 
  EXEC('
  CREATE PROCEDURE dbo.UpdateSalt 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE dbo.UpdateSalt
  @UserName VARCHAR(100)
, @Salt VARCHAR(100)
AS 
  UPDATE  mem
  SET     PasswordSalt = @Salt
  FROM    dbo.aspnet_Membership mem
  INNER JOIN dbo.aspnet_Users AS usr ON mem.UserId = usr.UserId
  WHERE   usr.LoweredUserName = @UserName

 GO


IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'dbo'
                        AND obj.name = 'UpdatePassword' ) 
  EXEC('
  CREATE PROCEDURE dbo.UpdatePassword 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE dbo.UpdatePassword
  @UserName VARCHAR(100)
, @Password VARCHAR(100)
AS 
  UPDATE  mem
  SET     Password = @Password
  FROM    dbo.aspnet_Membership mem
  INNER JOIN dbo.aspnet_Users AS usr ON mem.UserId = usr.UserId
  WHERE   usr.LoweredUserName = @UserName

 GO


IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'dbo'
                        AND obj.name = 'GetPasswordInfo' ) 
  EXEC('
  CREATE PROCEDURE dbo.GetPasswordInfo 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE dbo.GetPasswordInfo @UserName VARCHAR(100)
AS 
  SELECT  PasswordSalt
        , Password
  FROM    dbo.aspnet_Membership AS mem
  INNER JOIN dbo.aspnet_Users AS usr ON mem.UserId = usr.UserId
  WHERE   usr.LoweredUserName = @UserName
GO
 
 
 
IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'AddImage' ) 
  EXEC('
  CREATE PROCEDURE daycare.AddImage 
  AS 
  SELECT 1
  ') 
GO 
ALTER PROCEDURE daycare.AddImage @FileName VARCHAR(200)
AS 
  INSERT  INTO daycare.Images
          (FileName)
  VALUES  (@FileName)
  SELECT  SCOPE_IDENTITY();
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'dbo'
                        AND obj.name = 'GetAllRoles' ) 
  EXEC('
  CREATE PROCEDURE dbo.GetAllRoles 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE dbo.GetAllRoles
  @LoweredApplicationName VARCHAR(100)
AS 
  SELECT  rol.RoleName
        , rol.RoleId
        , rol.Description
  FROM    dbo.aspnet_Roles AS rol
  INNER JOIN dbo.aspnet_Applications AS app ON rol.ApplicationId = app.ApplicationId
  WHERE   app.LoweredApplicationName = @LoweredApplicationName
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'AddDocumentType' ) 
  EXEC('
  CREATE PROCEDURE daycare.AddDocumentType 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.AddDocumentType
  @DocumentTypeName VARCHAR(100)
AS 
  IF (
      NOT EXISTS ( SELECT *
                   FROM   daycare.DocumentTypes
                   WHERE  DocumentTypeName = @DocumentTypeName )
     ) 
    BEGIN
      INSERT  INTO daycare.DocumentTypes
      VALUES  (@DocumentTypeName)
    END
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'UpdateDocumentType' ) 
  EXEC('
  CREATE PROCEDURE daycare.UpdateDocumentType
  AS 
  SELECT 1
  ')
  GO
ALTER PROCEDURE daycare.UpdateDocumentType
  @DocumentTypeId INT
, @DocumentTypeName VARCHAR(100)
AS 
  UPDATE  daycare.DocumentTypes
  SET     DocumentTypeName = @DocumentTypeName
  WHERE   DocumentTypeId = @DocumentTypeId
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'DeleteDocumentType' ) 
  EXEC('
  CREATE PROCEDURE daycare.DeleteDocumentType
  AS 
  SELECT 1
  ')
  GO
ALTER PROCEDURE daycare.DeleteDocumentType @DocumentTypeId INT
AS 
  DELETE  FROM daycare.DocumentTypes
  WHERE   DocumentTypeId = @DocumentTypeId
  GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetDocumentTypes' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetDocumentTypes 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.GetDocumentTypes
AS 
  SELECT  DocumentTypeId
        , DocumentTypeName
  FROM    daycare.DocumentTypes
GO


IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetDocumentType' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetDocumentType 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.GetDocumentType @DocumentTypeId INT
AS 
  SELECT  DocumentTypeId
        , DocumentTypeName
  FROM    daycare.DocumentTypes
  WHERE   DocumentTypeId = @DocumentTypeId
GO


IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetEmployeeDocuments' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetEmployeeDocuments 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.GetEmployeeDocuments
  @EmployeeId INT
, @IncludeExpiredDocuments BIT
, @DocumentTypeId INT = NULL
AS 
  IF @IncludeExpiredDocuments = 0 
    BEGIN
      SELECT  doc.DocumentId
            , doc.DocumentName
            , doc.FileName
            , doc.DocumentTypeId
            , doc.ExpirationDate
      FROM    daycare.Documents AS doc
      INNER JOIN daycare.EmployeeDocuments AS ed ON doc.DocumentId = ed.DocumentId
      WHERE   ed.EmployeeId = @EmployeeId
              AND (
                   doc.DocumentTypeId = @DocumentTypeId
                   OR @DocumentTypeId IS NULL
                  )
              AND CONVERT(DATETIME,doc.ExpirationDate) >= GETDATE() 
    END
  ELSE 
    BEGIN
      SELECT  doc.DocumentId
            , doc.DocumentName
            , doc.FileName
            , doc.DocumentTypeId
            , doc.ExpirationDate
      FROM    daycare.Documents AS doc
      INNER JOIN daycare.EmployeeDocuments AS ed ON doc.DocumentId = ed.DocumentId
      WHERE   ed.EmployeeId = @EmployeeId
              AND (
                   doc.DocumentTypeId = @DocumentTypeId
                   OR @DocumentTypeId IS NULL
                  )
    END
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'AddDocument' ) 
  EXEC('
  CREATE PROCEDURE daycare.AddDocument 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.AddDocument
  @DocumentName VARCHAR(100)
, @FileName VARCHAR(100)
, @DocumentTypeId INT
, @ExpirationDate DATETIME = NULL
AS 
  INSERT  INTO daycare.Documents
          (
           DocumentName
         , FileName
         , DocumentTypeId
         , ExpirationDate
          )
  VALUES  (
           @DocumentName
         , @FileName
         , @DocumentTypeId
         , @ExpirationDate
          )
  SELECT  @@IDENTITY;
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'AddEmployeeDocument' ) 
  EXEC('
  CREATE PROCEDURE daycare.AddEmployeeDocument 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.AddEmployeeDocument
  @EmployeeId INT
, @DocumentId INT
AS 
  INSERT  INTO daycare.EmployeeDocuments
          (EmployeeId,DocumentId)
  VALUES  (@EmployeeId,@DocumentId)
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetDocument' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetDocument 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.GetDocument @DocumentId INT
AS 
  SELECT  doc.DocumentId
        , doc.DocumentName
        , doc.FileName
        , doc.DocumentTypeId
        , doc.ExpirationDate
  FROM    daycare.Documents AS doc
  WHERE   doc.DocumentId = @DocumentId
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'DeleteDocument' ) 
  EXEC('
  CREATE PROCEDURE daycare.DeleteDocument 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.DeleteDocument @DocumentId INT
AS 
  DELETE  FROM daycare.EmployeeDocuments
  WHERE   DocumentId = @DocumentId
  DELETE  FROM daycare.Documents
  WHERE   DocumentId = @DocumentId
GO


IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'UpdateDocument' ) 
  EXEC('
  CREATE PROCEDURE daycare.UpdateDocument 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.UpdateDocument
  @DocumentId INT
, @DocumentName VARCHAR(100)
, @ExpirationDate VARCHAR(100) = NULL
, @DocumentTypeId INT
AS 
  UPDATE  daycare.Documents
  SET     DocumentName = @DocumentName
        , ExpirationDate = @ExpirationDate
        , DocumentTypeId = @DocumentTypeId
  WHERE   DocumentId = @DocumentId
  GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetConfigurationSettings' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetConfigurationSettings 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.GetConfigurationSettings
  @ConfigurationSettingsGroupId INT = NULL
AS 
  SELECT  cs.ConfigurationSettingsId
        , cs.ConfigurationSettingsName
        , cs.ConfigurationSettingsDescription
        , cs.ConfigurationSettingsGroupId
        , csg.ConfigurationsettingsGroupName
  FROM    daycare.ConfigurationSettings AS cs
  INNER JOIN daycare.ConfigurationSettingsGroups AS csg ON cs.ConfigurationSettingsGroupid = csg.ConfigurationSettingsGroupId
  WHERE   (
           cs.ConfigurationSettingsId = @ConfigurationSettingsGroupId
           OR @ConfigurationSettingsGroupId IS NULL
          )

GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetAllChildren' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetAllChildren 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.GetAllChildren
  @ExcludeInactiveFamilies BIT
AS 
  SELECT  chi.ChildId
        , chi.FamilyId
        , chi.FirstName
        , chi.LastName
        , chi.NickName
        , chi.DateOfBirth
        , chi.IsActive
        , chi.ImageId
        , chi.FirstName + ' ' + chi.LastName AS FullName
  FROM    daycare.Children AS chi
  INNER JOIN daycare.Families AS fam ON chi.FamilyId = fam.FamilyId
  WHERE   fam.IsActive = @ExcludeInactiveFamilies
          OR fam.IsActive = 1
--Always include active families
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetFamilyChildren' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetFamilyChildren 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.GetFamilyChildren @ChildId INT
AS 
  SELECT  chi.ChildId
        , chi.FamilyId
        , chi.FirstName
        , chi.LastName
        , chi.NickName
        , chi.DateOfBirth
        , chi.IsActive
        , chi.ImageId
        , FirstName + ' ' + LastName AS FullName
  FROM    daycare.Families AS fam
  INNER JOIN daycare.Children AS chi ON fam.FamilyId = chi.FamilyId
  WHERE   chi.ChildId = @ChildId
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetFamilyAdults' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetFamilyAdults 
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.GetFamilyAdults @ChildId INT
AS 
  SELECT  adu.ChildContactId
        , adu.FamilyId
        , adu.FirstName
        , adu.LastName
        , adu.EmailAddress
        , adu.IsEmergencyContact
        , adu.IsApprovedPickup
        , adu.ImageId
        , adu.FirstName + ' ' + adu.LastName AS FullName
  FROM    daycare.ChildContacts AS adu
  INNER JOIN daycare.Children AS chi ON adu.FamilyId = chi.FamilyId
  WHERE   chi.ChildId = @ChildId
  GO

  
IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetFamily' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetFamily
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.GetFamily @ChildId INT
AS 
  SELECT  fam.FamilyId
        , fam.IsActive
        , fam.EnforceApprovedPickupList
        , fam.MailingAddressId
        , BillingAddressId
        , ChildId
  FROM    daycare.Families AS fam
  INNER JOIN daycare.Children AS chi ON fam.FamilyId = chi.FamilyId
  WHERE   chi.ChildId = @ChildId
  GO

  IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'AddEmployeeNote' ) 
  EXEC('
  CREATE PROCEDURE daycare.AddEmployeeNote
  AS 
  SELECT 1
  ') 
GO
ALTER PROCEDURE daycare.AddEmployeeNote
@EmployeeId INT,
@NotedById INT,
@NoteText VARCHAR(1000)
AS

INSERT INTO daycare.EmployeeNotes
(TimeStamp, EmployeeId, NotedById, NoteText)
Values
(GETDATE(), @EmployeeId, @NotedById, @NoteText)
GO


IF NOT EXISTS ( SELECT  *
                FROM    sys.objects AS obj
                INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
                WHERE   type = 'P'
                        AND sch.name = 'daycare'
                        AND obj.name = 'GetEmployeeNotes' ) 
  EXEC('
  CREATE PROCEDURE daycare.GetEmployeeNotes
  AS 
  SELECT 1
  ') 
GO

ALTER PROCEDURE daycare.GetEmployeeNotes
@EmployeeId INT
AS
SELECT * FROM daycare.EmployeeNotes
WHERE EmployeeId = @EmployeeId
ORDER BY Timestamp desc
GO

/* Data Updates */

/* Clear existing data */
DELETE  FROM dbo.aspnet_UsersInRoles
DELETE  FROM dbo.aspnet_Roles
DELETE  FROM dbo.aspnet_Profile
DELETE  FROM dbo.aspnet_Membership
DELETE  FROM dbo.aspnet_Users
DELETE  FROM dbo.aspnet_Applications

DELETE  FROM daycare.EmployeeDocuments
DELETE  FROM daycare.Employees
	
ALTER TABLE daycare.ChildContacts DROP CONSTRAINT FK_Adults_Images
ALTER TABLE daycare.Children DROP CONSTRAINT FK_Children_Images
ALTER TABLE daycare.Employees DROP CONSTRAINT FK_Employees_Images
TRUNCATE TABLE daycare.Images
ALTER TABLE daycare.Employees ADD CONSTRAINT FK_Employees_Images FOREIGN KEY (ImageId) REFERENCES daycare.Images(ImageId)
ALTER TABLE daycare.Employees ADD CONSTRAINT FK_Adults_Images FOREIGN KEY (ImageId) REFERENCES daycare.Images(ImageId)
ALTER TABLE daycare.Employees ADD CONSTRAINT FK_Children_Images FOREIGN KEY (ImageId) REFERENCES daycare.Images(ImageId)

DELETE  FROM daycare.Documents
DELETE  FROM daycare.DocumentTypes
GO
/* Membership Setup */

/* Variables */
DECLARE @AppName VARCHAR(100) = 'Daycare';
DECLARE @AppId UNIQUEIDENTIFIER;
DECLARE @AdminUserId UNIQUEIDENTIFIER;
DECLARE @Timestamp DATETIME2 = GETDATE();
	
/* Application Setup */

EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName = @AppName,@ApplicationId = @AppId
	
UPDATE  dbo.aspnet_Applications
SET     Description = 'The Daycare application serves to manage attendance, billing and record keeping for small to medium Daycares'
WHERE   ApplicationName = @AppName

SET @AppId = (
              SELECT  ApplicationId
              FROM    dbo.aspnet_Applications
              WHERE   ApplicationName = @AppName
             )
PRINT 'AppId: ';
PRINT @AppId
/* User Setup */
	
EXEC dbo.aspnet_Users_CreateUser @ApplicationId = @AppId,@UserName = 'admin',@IsUserAnonymous = 0,@LastActivityDate = @Timestamp,@UserId = @AdminUserId 
	
SET @AdminUserId = (
                    SELECT  UserId
                    FROM    dbo.aspnet_Users
                    WHERE   UserName = 'admin'
                   )
	
PRINT 'AdminUserId: '
PRINT @AdminUserId
/* Role Setup */

EXEC dbo.aspnet_Roles_CreateRole @AppName,'Accounting'
EXEC dbo.aspnet_Roles_CreateRole @AppName,'Reporting'
EXEC dbo.aspnet_Roles_CreateRole @AppName,'Personnel'
EXEC dbo.aspnet_Roles_CreateRole @AppName,'CustomerService'
EXEC dbo.aspnet_Roles_CreateRole @AppName,'Administration'
	
UPDATE  dbo.aspnet_Roles
SET     Description = 'Employees with the accounting role have access to view and generate family invoices and also view and generate all employees payroll information. Employees without this role will still be able to view their own payroll information.'
WHERE   RoleName = 'Accounting'
UPDATE  dbo.aspnet_Roles
SET     Description = 'Employees with the administration role are able to create new staff members as well as manage the passwords of existing employees.'
WHERE   RoleName = 'Administration'
UPDATE  dbo.aspnet_Roles
SET     Description = 'Employees with the customer service role have access to family personnel records.'
WHERE   RoleName = 'CustomerService'
UPDATE  dbo.aspnet_Roles
SET     Description = 'Employees with the personnel role have access to staff members'' records. Employees without this role will still be able to access their own information.'
WHERE   RoleName = 'Personnel'
UPDATE  dbo.aspnet_Roles
SET     Description = 'Employees with the reporting role have access to run reports for the company.'
WHERE   RoleName = 'Reporting'

	
/* Assign User to Roles */
EXEC dbo.aspnet_UsersInRoles_AddUsersToRoles @ApplicationName = @AppName,@UserNames = 'admin',@RoleNames = 'Administration',@CurrentTimeUtc = @Timestamp
EXEC dbo.aspnet_UsersInRoles_AddUsersToRoles @ApplicationName = @AppName,@UserNames = 'admin',@RoleNames = 'Personnel',@CurrentTimeUtc = @Timestamp

/* Create Membership User */
	
EXEC dbo.aspnet_Membership_CreateUser @ApplicationName = @AppName,@UserName = 'admin',@Password = '4XtPp5DXdI3poc4JnqPVV/Xs3a4=',@PasswordSalt = 'AGteAQWU3MwhvHPOZ5owHg==',@Email = 'jonathan.turner82@gmail.com',@PasswordQuestion = '',@PasswordAnswer = '',@IsApproved = 1,@CurrentTimeUtc = @Timestamp,@CreateDate = @Timestamp,@UniqueEmail = 1,@PasswordFormat = 1,@UserId = @AdminUserId

/* Create Profile For Admin User */

EXEC dbo.aspnet_Profile_SetProperties @ApplicationName = @AppName,@PropertyNames = 'EmployeeId:S:0:1',@PropertyValuesString = '1',@PropertyValuesBinary = '0x',@UserName = 'admin',@IsUserAnonymous = 0,@CurrentTimeUtc = @Timestamp


/* Default Image */
	
EXEC daycare.AddImage @FileName = "noImage.png"
	
/* Documentation Type */
	
EXEC daycare.AddDocumentType @DocumentTypeName = "Certification"
EXEC daycare.AddDocumentType @DocumentTypeName = "Resume"

/* Configuration Settings */

INSERT  INTO daycare.ConfigurationSettingsGroups
        (
         ConfigurationSettingsGroupName
        )
VALUES  (
         'Documents'
        )

INSERT  INTO daycare.ConfigurationSettings
        (
         ConfigurationSettingsName
       , ConfigurationSettingsDescription
       , ConfigurationSettingsGroupId
        )
VALUES  (
         'Document Types'
       , 'Document types allow for sorting and filtering of uploaded documents for employees and staff.'
       , (
          SELECT  ConfigurationSettingsGroupId
          FROM    daycare.ConfigurationSettingsGroups
          WHERE   ConfigurationSettingsGroupName = 'Documents'
         )
        )